import java.sql.*;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) {
//0913课上任务
        //查询
        m1();
       //条件查询
        m2();
        //增加
        m3();
        //删除
        m4();
        //修改
        m5();


        //1.加载驱动
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//            //2.获取连接(url username password)
//            String url="jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
//            Connection connection = DriverManager.getConnection(url, "root", "526874");
////            if (connection!=null){
////                System.out.println("连接成功");
////            }
//            //3.写sql
//            String sql="select * from t1";
//            //4.预编译sql
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            //5.有参数设置参数
//            //6.执行sql
//            ResultSet resultSet = preparedStatement.executeQuery();
//            //7.遍历结果集
//            while(resultSet.next()){
//                int id = resultSet.getInt(1);
//                String name = resultSet.getString(2);
//                String pass = resultSet.getString(3);
//                System.out.println(id+"------"+name+"------"+pass);
//            }
//            //8.关闭资源
//            resultSet.close();
//            preparedStatement.close();
//            connection.close();
//        } catch (ClassNotFoundException e) {
//            throw new RuntimeException(e);
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//    }
    }



    private static void m1() {
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url username password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection = DriverManager.getConnection(url, "root", "526874");
//            if (connection!=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql = "select * from t1";
            //4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            //6.执行sql
            ResultSet resultSet = preparedStatement.executeQuery();
            //7.遍历结果集
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String pass = resultSet.getString(3);
                System.out.println(id + "------" + name + "------" + pass);
            }
            //8.关闭资源
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static void m2() {
        try {
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入你要查询的姓名");
            String name1=sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url username password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection = DriverManager.getConnection(url, "root", "526874");
//            if (connection!=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql = "select * from t1 where name like ?";
            //4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            preparedStatement.setObject(1,"%"+name1+"%");
            //6.执行sql
            ResultSet resultSet = preparedStatement.executeQuery();
            //7.遍历结果集
            while (resultSet.next()) {//获取
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String pass = resultSet.getString(3);
                System.out.println(id + "------" + name + "------" + pass);
            }
            //8.关闭资源
            resultSet.close();//查询需要写
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static void m3 () {
        //1.加载驱动
        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入要新增的id：");
            int id=sc.nextInt();
            System.out.println("请输入要新增的姓名：");
            String name = sc.next();
            System.out.println("请输入要新增的密码：");
            String pass = sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url username password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection = DriverManager.getConnection(url, "root", "526874");
//            if (connection!=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql = "insert into t1(id,name,pass) values(?,?,?)";
            //4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            preparedStatement.setObject(1, id);
            preparedStatement.setObject(2,name);
            preparedStatement.setObject(3,pass);
            //6.执行sql
            int result = preparedStatement.executeUpdate();
            //7.结果判断
            if (result > 0) {
                System.out.println("新增成功！");
            }
            //8.关闭资源
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static void m4 () {
        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入要删除的id：");
            int id = sc.nextInt();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url username password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection = DriverManager.getConnection(url, "root", "526874");
//            if (connection!=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql = "delete from t1 where id=?";
            //4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            preparedStatement.setObject(1,id);
            //6.执行sql
            int result = preparedStatement.executeUpdate();
            //7.结果判断
            if (result > 0) {
                System.out.println("删除成功！");
            }
            //8.关闭资源
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //添加的修改
    private static void m5() {
        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("请输入要修改的id：");
            int id = sc.nextInt();
            System.out.println("请输入要修改的信息(1.姓名 2.密码)：");
            int a=sc.nextInt();
            System.out.println("请输入修改后的信息：");
            String str=sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url username password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection connection = DriverManager.getConnection(url, "root", "526874");
//            if (connection!=null){
//                System.out.println("连接成功");
//            }
            //3.写sql
            String sql="";
            if(a==1){
                sql = "UPDATE t1 SET name =? WHERE id =?";
            } else if (a==2){
                sql = "UPDATE t1 SET pass =? WHERE id =?";
            }
            //4.预编译sql
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //5.有参数设置参数
            preparedStatement.setObject(1,str);
            preparedStatement.setObject(2,id);
            //6.执行sql
            int result = preparedStatement.executeUpdate();//注意update query区别
            //7.结果判断
            if (result > 0) {
                System.out.println("更改成功！");
            }
            //8.关闭资源
            preparedStatement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}